const xss = require('xss')
const { exec } = require('../db/mysql')


const getList = async (userId) => { 
    let sql = `select DISTINCT a.id,a.user_id AS userId, date_format(a.createtime, '%Y-%m-%d %H:%i:%s') AS createtime,a.content,a.pid,d.id AS doctorId,d.truename,d.hospname,d.deptname,d.positional,d.doctorphoto,d.description from messages a left join doctors d  on a.doctor_id = d.id ` 
	if(0!=userId){
		sql +=`where a.user_id = ${userId} `
	}
	sql +=` GROUP BY d.id order by a.createtime ASC;`
	return await exec(sql) 
} 

const last = async (userId,doctorId) => { 
    let sql = `select id from messages where user_id = ${userId} and doctor_id = ${doctorId}  order by createtime DESC LIMIT 1;`
	const rows = await exec(sql) 
	return rows[0]
} 


const getDetail = async (id) => {
    const sql = 
	`SELECT ID.level, DATA.id,DATA.user_id AS userId,DATA.content,date_format(DATA.createtime, '%Y-%m-%d %H:%i:%s') AS createtime,DATA.pid,DATA.doctor_id AS doctorId FROM( 
   SELECT 
   @ids as _ids, 
   ( SELECT @ids := GROUP_CONCAT(id) 
   FROM messages 
   WHERE FIND_IN_SET(pid, @ids) 
   ) as cids, 
   @l := @l+1 as level 
   FROM messages, 
   (SELECT @ids :=${id}, @l := 0 ) b 
   WHERE @ids IS NOT NULL 
   ) id, messages DATA 
   WHERE FIND_IN_SET(DATA.id, ID._ids) 
   ORDER BY level, id `
   
	const rows = await exec(sql) 
    return rows
}

const newMessage = async (messageData = {}) => { 
    const id = xss(messageData.id) 
    const userId = xss(messageData.userId) 
    const doctorId = xss(messageData.doctorId)  
    const content = xss(messageData.content)  
    let sql = `insert into messages (user_id, createtime,doctor_id,content)
        values ('${userId}', now(), '${doctorId}', '${content}');` 
	if(id){
		sql = `insert into messages (user_id, createtime,doctor_id,content,pid)
		    values ('${userId}', now(), '${doctorId}', '${content}',${id});` 
	}
    const insertData = await exec(sql)
	
    return {
        id: insertData.insertId
    }
}


const updateBlog = async (id, blogData = {}) => {
    // id 就是要更新博客的 id
    // blogData 是一个博客对象，包含 title content 属性

    const title = xss(blogData.title)
    const content = xss(blogData.content)

    const sql = `
        update blogs set title='${title}', content='${content}' where id=${id}
    `

    const updateData = await exec(sql)
    if (updateData.affectedRows > 0) {
        return true
    }
    return false
}

const countAppointment = async (appointmentData = {}) => { 
	const userId = xss(appointmentData.userId)
	const doctorId = xss(appointmentData.doctorId) 
    const sql = `select count(*) AS count  from appointments where valid = 1 and user_id='${userId}' and doctor_id='${doctorId}';`  
	const rows = await exec(sql)
	return rows[0].count
}

const totalAppointment = async (appointmentData = {}) => { 
	const userId = xss(appointmentData.userId) 
    const sql = `select count(*) AS count  from appointments where valid = 1 and user_id='${userId}';`  
	const rows = await exec(sql)
	return rows[0].count
}

module.exports = {
    getList,
    getDetail,
    newMessage,
    updateBlog,
    countAppointment,
	totalAppointment,
	last
}